package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaWhsDumpQueryDTO;
import com.sunda.spmsoversea.entity.OverseaWhsDump;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 海外仓转储任务表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-11-02
 */
@Mapper
public interface OverseaWhsDumpMapper extends BaseMapper<OverseaWhsDump> {

    @Select("<script>" +
            "SELECT OWD.WHS_DUMP_UUID AS \"whsDumpUuid\",\n" +
            "       OWD.WHS_DUMP_NO AS \"whsDumpNo\",\n" +
            "       OWD.WERKS_DUMP_IN AS \"werksDumpIn\",\n" +
            "       OWD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCodeDumpIn\",\n" +
            "       OWD.WERKS_DUMP_OUT AS \"werksDumpOut\",\n" +
            "       OWD.WHS_LOCATION_CODE_DUMP_OUT AS \"whsLocationCodeDumpOut\",\n" +
            "       OWD.CREATE_DATE AS \"createDate\",\n" +
            "       OWD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWD.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       OWD.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       OWD.OA_REMARK AS \"oaRemark\",\n" +
            "       OWD.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       OWD.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       OWD.DUMP_OUT_REMARK AS \"dumpOutRemark\",\n" +
            "       OWD.DUMP_OUT_DATE AS \"dumpOutDate\",\n" +
            "       OWD.SAP_PURCHASE_NO AS \"sapPurchaseNo\",\n" +
            "       OWD.PURCHASE_POSTING_DATE AS \"purchasePostingDate\",\n" +
            "       OWD.PURCHASE_VOUCHER_YEAR AS \"purchaseVoucherYear\",\n" +
            "       OWD.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWD.POSTING_DATE AS \"postingDate\",\n" +
            "       OWD.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWD.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OWD.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OWD.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OWD.CREATE_USERID AS \"createUserid\",\n" +
            "       OWD.DATA_VERSION AS \"dataVersion\"\n" +
            "FROM OVERSEA_WHS_DUMP OWD\n" +
            "WHERE 1=1\n" +
            "  <when test='dto.whsDumpNo!=null and dto.whsDumpNo != \"\"'> AND OWD.WHS_DUMP_NO = #{dto.whsDumpNo}</when>" +
            "  <when test='dto.werksDumpIn!=null and dto.werksDumpIn != \"\"'> AND OWD.WERKS_DUMP_IN = #{dto.werksDumpIn}</when>" +
            "  <when test='dto.whsLocationCodeDumpIn!=null and dto.whsLocationCodeDumpIn != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_IN = #{dto.whsLocationCodeDumpIn}</when>" +
            "  <when test='dto.werksDumpOut!=null and dto.werksDumpOut != \"\"'> AND OWD.WERKS_DUMP_OUT = #{dto.werksDumpOut}</when>" +
            "  <when test='dto.whsLocationCodeDumpOut!=null and dto.whsLocationCodeDumpOut != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_OUT = #{dto.whsLocationCodeDumpOut}</when>" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND OWD.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND OWD.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWD.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.sapPurchaseNo!=null and dto.sapPurchaseNo != \"\"'> AND OWD.SAP_PURCHASE_NO = #{dto.sapPurchaseNo}</when>" +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND OWD.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  AND OWD.SPMS_STATUS != '0' ORDER BY OWD.SPMS_STATUS ASC, OWD.DATA_VERSION DESC " +
            "</script>")
    List<Map<String, Object>> getWhsDumpPage(IPage page, @Param("dto") OverseaWhsDumpQueryDTO dto);

    @Select("SELECT MAX(ITEM) FROM OVERSEA_WHS_DUMP_DTL WHERE WHS_DUMP_UUID = #{whsDumpUuid}")
    Integer getWhsDumpMaxItemNumber(@Param("whsDumpUuid") String whsDumpUuid);

    /** Z_SPMS_CREATE_STO 转储任务提交SAP获取PO，请求表头结构 */
//    @Select("SELECT REPLACE(OWD.CREATE_DATE, '-', '') AS \"aedat\",\n" +
//            "       OWD.WERKS_DUMP_OUT AS \"werks\",\n" +
//            "       OWD.PURCHASE_GROUP AS \"ekgrp\",\n" +
//            "       OWD.VOUCHER_TYPE AS \"bsart\",\n" +
//            "       '' AS \"zterm\",\n" +
//            "       W.COMPANY_CODE AS \"bukrs\",\n" +
//            "       OWD.PURCHASE_ORG AS \"ekorg\",\n" +
//            "       OWD.SAP_PURCHASE_NO AS \"ebeln\",\n" +
//            "       '海外转储单：' || OWD.WHS_DUMP_NO AS \"zstring\"\n" +
//            "FROM OVERSEA_WHS_DUMP OWD LEFT JOIN WERKS W ON OWD.WERKS_DUMP_OUT = W.WERKS\n" +
//            "WHERE OWD.WHS_DUMP_UUID = #{whsDumpUuid} ")
//    Map<String, Object> getPoHead(@Param("whsDumpUuid") String whsDumpUuid);

    /** Z_POS_PO_CREATE 转储任务提交SAP获取PO，请求表头结构 */
    @Select("SELECT W.COMPANY_CODE AS \"COMP_CODE\",\n" +
            "       OWD.VOUCHER_TYPE AS \"DOC_TYPE\",\n" +
            "       W2.SUPPLIER_NO AS \"VENDOR\",\n" +
            "       OWD.WERKS_DUMP_OUT AS \"SUPPL_PLNT\",\n" +
            "       REPLACE(OWD.CREATE_DATE, '-', '') AS \"CREAT_DATE\",\n" +
            "       OWD.PURCHASE_ORG AS \"PURCH_ORG\",\n" +
            "       OWD.PURCHASE_GROUP AS \"PUR_GROUP\"\n" +
            "FROM OVERSEA_WHS_DUMP OWD\n" +
            "    LEFT JOIN WERKS W ON OWD.WERKS_DUMP_IN = W.WERKS\n" +
            "    LEFT JOIN WERKS W2 ON OWD.WERKS_DUMP_OUT = W2.WERKS\n" +
            "WHERE OWD.WHS_DUMP_UUID = #{whsDumpUuid} ")
    Map<String, Object> getSapPoHead(@Param("whsDumpUuid") String whsDumpUuid);

    /** 转储任务提交SAP获取PO，请求行项目结构 */
//    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"ebelp\",\n" +
//            "       TO_CHAR(SYSDATE + 7, 'yyyyMMdd') AS \"eindt\",\n" +
//            "       OWD.WERKS_DUMP_OUT AS \"werks\",\n" +
//            "       OWD.WHS_LOCATION_CODE_DUMP_OUT AS \"lgort\",\n" +
//            "       OWDD.MATERIAL_NO AS \"matnr\",\n" +
//            "       TO_CHAR(OWDD.ACTUAL_QTY_BASIC_UNIT) AS \"menge\",\n" +
//            "       OWDD.BASIC_UNIT AS \"meins\",\n" +
//            "       '' AS \"loekz\",\n" +
//            "       '' AS \"uebto\",\n" +
//            "       'X' AS \"webre\",\n" +
//            "       '' AS \"lfart\",\n" +
//            "       '' AS \"vsbed\",\n" +
//            "       '' AS \"ladgr\",\n" +
//            "       '' AS \"tragr\",\n" +
//            "       '' AS \"vkorg\",\n" +
//            "       '' AS \"vtweg\",\n" +
//            "       '' AS \"spart\",\n" +
//            "       '' AS \"kunnr\"\n" +
//            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
//            "    LEFT JOIN OVERSEA_WHS_DUMP OWD ON OWDD.WHS_DUMP_UUID = OWD.WHS_DUMP_UUID\n" +
//            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
//            "  AND OWDD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
//            "ORDER BY OWDD.ITEM ASC ")
//    List<Map<String, Object>> getPoItem(@Param("whsDumpUuid") String whsDumpUuid);

    /** Z_POS_PO_CREATE 转储任务提交SAP获取PO，请求行项目结构 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"PO_ITEM\",\n" +
            "       OWDD.MATERIAL_NO AS \"MATERIAL\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"SHORT_TEXT\",\n" +
            "       OWD.WERKS_DUMP_IN AS \"PLANT\",\n" +
            "       TO_CHAR(OWDD.ACTUAL_QTY_BASIC_UNIT) AS \"QUANTITY\",\n" +
            "       OWDD.BASIC_UNIT AS \"PO_UNIT\",\n" +
            "       REPLACE(OWD.DELIVERY_DATE, '-', '') AS \"DELIVERY_DATE\",\n" +
            "       OWD.WHS_DUMP_NO AS \"WHS_DUMP_NO\"\n" +
            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
            "    LEFT JOIN OVERSEA_WHS_DUMP OWD ON OWDD.WHS_DUMP_UUID = OWD.WHS_DUMP_UUID\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON OWDD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
            "  AND OWDD.ACTUAL_QTY_BASIC_UNIT > 0 ORDER BY OWDD.ITEM ASC ")
    List<Map<String, Object>> getSapPoItem(@Param("whsDumpUuid") String whsDumpUuid);

    /** Z_POS_PO_CREATE 转储任务提交SAP撤销PO，请求行项目结构 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"PO_ITEM\"\n" +
            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
            "  AND OWDD.ACTUAL_QTY_BASIC_UNIT > 0 ORDER BY OWDD.ITEM ASC ")
    List<Map<String, Object>> sapPoCancelItem(@Param("whsDumpUuid") String whsDumpUuid);
}
